package cn.com.callback

import cn.com.elite.EliteBean
import com.alibaba.fastjson.JSONObject
import org.slf4j.Logger
import org.slf4j.LoggerFactory

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse
import java.text.SimpleDateFormat

/**
 * 网聊数据汇总定时任务
 * 2023/5/30
 */

public class apiHSTChatDate implements ServiceBackInvoke {
    private static final Logger log = LoggerFactory.getLogger(apiHSTChatDate.class);

    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse httpServletResponse) {


        httpServletResponse.setCharacterEncoding("UTF-8");
        httpServletResponse.setContentType("application/json;charset=utf-8");
        JSONObject result = new JSONObject();


        log.debug("网聊数据汇总手动执行任务启动");
        try {
            Date date = new Date();
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

            String starttime = request.getParameter("starttime");
            String endtime = request.getParameter("endtime");
            if (starttime == "" || starttime == null) {
                starttime = dateFormat.format(date) + ' 00:00:00';
            } else {
                starttime = request.getParameter("starttime");
            }
            if (endtime == "" || endtime == null) {
                endtime = dateFormat.format(date) + ' 23:59:59';
            } else {
                endtime = request.getParameter("endtime");
            }
            log.debug("调用参数-开始时间:" + starttime + " 结束时间:" + endtime);

            /*String selectSql ="INSERT INTO chat_service_detail (\n" +
                    "rec_guid,\n" +
                    "creation_time,\n" +
                    "request_id,\n" +
                    "session_id,\n" +
                    "queue_id,\n" +
                    "client_id,\n" +
                    "isvip,\n" +
                    "session_starttime,\n" +
                    "session_endtime,\n" +
                    "robot_id,\n" +
                    "robot_time,\n" +
                    "isagent,\n" +
                    "agent_starttime,\n" +
                    "agent_wait_time,\n" +
                    "agent_isgiveup,\n" +
                    "is_LEAVE_MSG,\n" +
                    "staff_id,\n" +
                    "agent_end_time,\n" +
                    "istransfer,\n" +
                    "transfer_session_id,\n" +
                    "agent_time,\n" +
                    "custtotal,\n" +
                    "max_response_time,\n" +
                    "min_response_time,\n" +
                    "agenttotal,\n" +
                    "is_Invite_comments,\n" +
                    "is_evaluate,\n" +
                    "rating,\n" +
                    "cyclesnum,\n" +
                    "response_time,\n" +
                    "request_online,\n" +
                    "visitor_give_up,\n" +
                    "no_wait_response,\n" +
                    "wait_response,\n" +
                    "online_response,\n" +
                    "wait_give_up,\n" +
                    "wait_to_message,\n" +
                    "total_wait,\n" +
                    "request_offline,\n" +
                    "url_from,\n" +
                    "wait_time,\n" +
                    "total,\n" +
                    "Content,\n" +
                    "rating_remarks,\n" +
                    "Summary_1,\n" +
                    "Summary_2,\n" +
                    "Summary_3,\n" +
                    "remker,\n" +
                    "close_by,\n" +
                    "robot_starttime,\n" +
                    "qbqqs,\n" +
                    "jqrzrgqqs,\n" +
                    "zjzrgqqs,\n" +
                    "zxjqrzrgqqs,\n" +
                    "zxzjzrgqqs,\n" +
                    "lxjqrzrgnum,\n" +
                    "lxzjzrgnum,\n" +
                    "robot_lxs,\n" +
                    "createddate \n" +
                    ") SELECT DISTINCT\n" +
                    "upper(\n" +
                    "UUID()) aaaaaaa,\n" +
                    "r1.creation_time,\n" +
                    "ifnull( r1.id, '' ) AS id,\n" +
                    "ifnull( r1.chat_session_id, '' ) AS chat_session_id,\n" +
                    "ifnull( r1.queue_id, '' ) AS queue_id,\n" +
                    "ifnull( r1.client_id, '' ) AS client_id,\n" +
                    "ifnull( r3.isvip, '' ) AS isvip,\n" +
                    "ifnull( r4.start_time, 0 ) start_time,\n" +
                    "ifnull( r4.end_time, 0 ) end_time,\n" +
                    "ifnull( r5.robotid, '' ) robotid,\n" +
                    "ifnull( r5.robottime, 0 ) tttt,\n" +
                    "ifnull( r6.isagent, '' ) isagent,\n" +
                    "ifnull( r7.creation_time, 0 ),\n" +
                    "ifnull( r7.agent_wait_time, 0 ) agent_wait_time,\n" +
                    "ifnull( r6.agent_is_giveup, '' ) agent_is_giveup,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN r9.isleave_msg = '是' THEN\n" +
                    "'是' ELSE '否' \n" +
                    "END AS isleave_msg,\n" +
                    "ifnull( a.to_chat_user_id, '' ) AS staff_id,\n" +
                    "ifnull( from_unixtime( b.maxtime / 1000, '%Y-%m-%d %H:%i:%s' ), '' ) AS maxtime,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN c.istransfer > 0 THEN\n" +
                    "'是' ELSE '否' \n" +
                    "END AS istransfer,\n" +
                    "ifnull( c.chat_session_id, '' ) AS zj_chat_session_id,\n" +
                    "ifnull( d.rg_service_duration, 0 ) AS rg_service_duration,\n" +
                    "ifnull( e.cust_zrghsl, 0 ) AS cust_zrghsl,\n" +
                    "ifnull( f.maxtotal, 0 ) AS maxtotal,\n" +
                    "ifnull( f.mintotal, 0 ) AS mintotal,\n" +
                    "ifnull( g.zx_send_sl, '' ) AS zx_send_sl,\n" +
                    "ifnull( h.是否邀评, '' ) AS isyp,\n" +
                    "ifnull( i.是否评价, '' ) AS israting,\n" +
                    "CASE\n" +
                    "i.评价满意度 \n" +
                    "WHEN 1 THEN\n" +
                    "'非常满意' \n" +
                    "WHEN 2 THEN\n" +
                    "'满意' \n" +
                    "WHEN 3 THEN\n" +
                    "'一般' \n" +
                    "WHEN 4 THEN\n" +
                    "'不满意' \n" +
                    "WHEN 5 THEN\n" +
                    "'非常不满意' ELSE '未评价' \n" +
                    "END AS rating,\n" +
                    "ifnull( r8.custtotal, 0 ) AS custtotal,\n" +
                    "ifnull( r8.response_time, 0 ) AS response_time,\n" +
                    "ifnull( r15.在线请求数, 0 ) AS zxnum,\n" +
                    "ifnull( k.访客放弃数, 0 ) AS fknum,\n" +
                    "ifnull( l.未等待应答数, 0 ) AS notwaitnum,\n" +
                    "ifnull( m.等待应答数, 0 ) AS waitnum,\n" +
                    "ifnull( n.在线应答数, 0 ) AS zxydnum,\n" +
                    "ifnull( o.等待放弃数, 0 ) AS waitfqnum,\n" +
                    "ifnull( p.等待转留言数, 0 ) AS waitlynum,\n" +
                    "ifnull( q.总等待数, 0 ) AS waittotal,\n" +
                    "ifnull( r16.离线请求数, 0 ) AS lxnum,\n" +
                    "ifnull( r10.url_from, '' ) AS url_from,\n" +
                    "ifnull( r11.wait_time, 0 ) AS wait_time,\n" +
                    "ifnull( r8.total, 0 ) AS total,\n" +
                    "ifnull( r8.Content, 0 ) AS Content,\n" +
                    "ifnull( i.满意度备注, '' ) AS rating_COMMENT,\n" +
                    "ifnull( r13.Summary_1, '' ) AS Summary_1,\n" +
                    "ifnull( r13.Summary_2, '' ) AS Summary_2,\n" +
                    "ifnull( r13.Summary_3, '' ) AS Summary_3,\n" +
                    "ifnull( r13.remker, '' ) AS remker,\n" +
                    "ifnull( r4.close_by, '' ) AS close_by,\n" +
                    "ifnull( r5.robot_starttime, 0 ) AS robot_starttime,\n" +
                    "ifnull( r14.全部请求数, 0 ) AS qbqqs,\n" +
                    "ifnull( r14.机器人转人工请求数, 0 ) AS jqrzrgqqs,\n" +
                    "ifnull( r14.直接转人工请求数, 0 ) AS zjzrgqqs,\n" +
                    "ifnull( r15.在线机器人转人工请求数, 0 ) AS zxjqrzrgqqs,\n" +
                    "ifnull( r15.在线直接转人工请求数, 0 ) AS zxzjzrgqqs,\n" +
                    "ifnull( r16.离线机器人转人工请求数, 0 ) AS lxjqrzrgnum,\n" +
                    "ifnull( r16.离线直接转人工请求数, 0 ) AS lxzjzrgnum,\n" +
                    "ifnull( r12.robot_lxs, 0 ) AS robot_lxs,\n" +
                    "now() \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "rank1,\n" +
                    "to_chat_user_id,\n" +
                    "id,\n" +
                    "client_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id,\n" +
                    "id,\n" +
                    "client_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "to_chat_user_id,\n" +
                    "id,\n" +
                    "client_id \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "AND cr.request_type_id != 5 \n" +
                    "ORDER BY\n" +
                    "chat_session_id ASC,\n" +
                    "creation_time DESC \n" +
                    ") t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") result \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "ORDER BY\n" +
                    "chat_session_id ASC \n" +
                    ") r1\n" +
                    "LEFT JOIN (\n" +
                    "SELECT DISTINCT\n" +
                    "chat_session_id,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN cv.visitor_guid IS NOT NULL THEN\n" +
                    "'否' ELSE '是' \n" +
                    "END AS isvip \n" +
                    "FROM\n" +
                    "chat_request cr\n" +
                    "LEFT JOIN chat_visitor cv ON cr.client_id = cv.visitor_guid \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "ORDER BY\n" +
                    "chat_session_id DESC \n" +
                    ") r3 ON r1.chat_session_id = r3.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "id,\n" +
                    "cs.start_time,\n" +
                    "cs.end_time,\n" +
                    "CASE\n" +
                    "WHEN LENGTH( cs.close_by )= 6 and cs.close_by not like 'BOT%' THEN\n" +
                    "'客服关闭' \n" +
                    "WHEN LENGTH( cs.close_by )= 36 THEN\n" +
                    "'客户关闭' \n" +
                    "WHEN  cs.close_by like 'BOT%' THEN\n" +
                    "'机器人关闭' \n" +
                    "WHEN cs.close_by = 'cleanupSessionsTimer' THEN\n" +
                    "'客户放弃' ELSE '' \n" +
                    "END AS close_by \n" +
                    "FROM\n" +
                    "chat_session cs \n" +
                    "WHERE\n" +
                    "cs.start_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cs.start_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    ") r4 ON r1.chat_session_id = r4.id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN cr.request_status_id = 12 THEN\n" +
                    "to_chat_user_id ELSE NULL \n" +
                    "END AS robotid,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN cr.request_status_id = 12 THEN\n" +
                    "( agent_end_time - end_time )/ 1000 ELSE NULL \n" +
                    "END robottime,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN cr.request_status_id = 12 THEN\n" +
                    "cr.creation_time ELSE NULL \n" +
                    "END robot_starttime \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "AND length( from_chat_user_id )> 6 \n" +
                    "ORDER BY\n" +
                    "1 DESC \n" +
                    ") r5 ON r1.chat_session_id = r5.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT DISTINCT\n" +
                    "cr1.chat_session_id,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN cr1.to_chat_user_id LIKE 'BOT%' THEN\n" +
                    "'否' ELSE '是' \n" +
                    "END AS isagent,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN cr1.to_chat_user_id IS NULL THEN\n" +
                    "'是' ELSE '否' \n" +
                    "END AS agent_is_giveup \n" +
                    "FROM\n" +
                    "chat_request cr\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "rank1,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "ORDER BY\n" +
                    "chat_session_id ASC,\n" +
                    "creation_time DESC \n" +
                    ") t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") result \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "ORDER BY\n" +
                    "chat_session_id DESC \n" +
                    ") cr1 ON cr1.chat_session_id = cr.chat_session_id \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "ORDER BY\n" +
                    "1 DESC \n" +
                    ") r6 ON r1.chat_session_id = r6.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cr1.chat_session_id,\n" +
                    "cr1.creation_time,\n" +
                    "( cr1.end_time - cr1.creation_time )/ 1000 AS agent_wait_time \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "end_time,\n" +
                    "queue_id,\n" +
                    "rank1,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "end_time,\n" +
                    "t.queue_id,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "end_time,\n" +
                    "queue_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "AND ((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "ORDER BY\n" +
                    "chat_session_id ASC,\n" +
                    "creation_time DESC \n" +
                    ") t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") result \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "ORDER BY\n" +
                    "chat_session_id DESC \n" +
                    ") cr1 \n" +
                    ") r7 ON r1.chat_session_id = r7.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "count( CASE WHEN length( chat_user_id )= 6 AND reply_delay > 0 THEN id ELSE NULL END ) AS custtotal,\n" +
                    "sum( CASE WHEN length( chat_user_id )= 6 AND reply_delay > 0 THEN reply_delay ELSE 0 END ) AS response_time,\n" +
                    "Count(*) AS Total,\n" +
                    "Sum( Content_Length ) AS Content \n" +
                    "FROM\n" +
                    "chat_message cm \n" +
                    "WHERE\n" +
                    "cm.Post_Time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cm.Post_Time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "GROUP BY\n" +
                    "chat_session_id \n" +
                    "ORDER BY\n" +
                    "chat_session_id DESC \n" +
                    ") r8 ON r1.chat_session_id = r8.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT DISTINCT\n" +
                    "cr.chat_session_id,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN clm.chat_request_id IS NOT NULL THEN\n" +
                    "'是' ELSE '否' \n" +
                    "END AS isleave_msg \n" +
                    "FROM\n" +
                    "chat_leave_msg clm\n" +
                    "LEFT JOIN chat_request cr ON cr.id = clm.chat_request_id \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "AND ((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "ORDER BY\n" +
                    "chat_session_id DESC \n" +
                    ") r9 ON r1.chat_session_id = r9.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT DISTINCT\n" +
                    "cr1.chat_session_id,\n" +
                    "cr1.to_chat_user_id \n" +
                    "FROM\n" +
                    "chat_request cr\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "rank1,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "ORDER BY\n" +
                    "chat_session_id ASC,\n" +
                    "creation_time DESC \n" +
                    ") t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") result \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "ORDER BY\n" +
                    "chat_session_id DESC \n" +
                    ") cr1 ON cr1.chat_session_id = cr.chat_session_id \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "AND ((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr1.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    ") A ON r1.chat_session_id = a.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cm.chat_session_id,\n" +
                    "max( post_time ) AS maxtime \n" +
                    "FROM\n" +
                    "chat_message cm \n" +
                    "WHERE\n" +
                    "cm.Post_Time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cm.Post_Time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "AND length( cm.chat_user_id )= 6 \n" +
                    "AND chat_user_id NOT LIKE 'BOT%' \n" +
                    "GROUP BY\n" +
                    "cm.chat_session_id \n" +
                    ") B ON a.chat_session_id = b.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cr.chat_session_id,\n" +
                    "sum( CASE WHEN request_type_id = 3 THEN 1 ELSE 0 END ) AS istransfer \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 GROUP BY cr.chat_session_id HAVING sum( CASE WHEN request_type_id = 3 THEN 1 ELSE 0 END ) > 0 \n" +
                    ") C ON a.chat_session_id = c.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT DISTINCT\n" +
                    "cr1.chat_session_id,\n" +
                    "( cr1.agent_end_time - cr1.end_time ) / 1000 AS rg_service_duration \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "rank1,\n" +
                    "to_chat_user_id,\n" +
                    "agent_end_time,\n" +
                    "end_time \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "t.agent_end_time,\n" +
                    "t.end_time,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "to_chat_user_id,\n" +
                    "agent_end_time,\n" +
                    "end_time \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "ORDER BY\n" +
                    "chat_session_id ASC,\n" +
                    "creation_time DESC \n" +
                    ") t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") result \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "ORDER BY\n" +
                    "chat_session_id DESC \n" +
                    ") cr1 \n" +
                    "WHERE\n" +
                    "1 = 1 \n" +
                    "AND ( cr1.to_chat_user_id NOT LIKE 'BOT%' AND cr1.to_chat_user_id IS NOT NULL ) \n" +
                    ") D ON a.chat_session_id = d.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cm.Chat_Session_Id AS Session_Id,\n" +
                    "count( DISTINCT cm.id ) AS 'cust_zrghsl' \n" +
                    "FROM\n" +
                    "chat_request cr\n" +
                    "LEFT JOIN Chat_Message cm ON cm.chat_session_id = cr.chat_session_id \n" +
                    "AND ( cr.request_type_id = 4 OR cr.request_type_id = 1 ) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "AND cm.post_time >= cr.creation_time \n" +
                    "AND LENGTH( chat_user_id )= 36 \n" +
                    "AND message_type = 0 \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "AND cm.chat_session_id IS NOT NULL \n" +
                    "GROUP BY\n" +
                    "cm.Chat_Session_Id \n" +
                    ") E ON a.chat_session_id = e.session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cm.Chat_Session_Id AS Session_Id,\n" +
                    "max( reply_delay ) maxtotal,\n" +
                    "min( CASE WHEN reply_delay > 0 THEN reply_delay END ) mintotal \n" +
                    "FROM\n" +
                    "chat_request cr\n" +
                    "LEFT JOIN Chat_Message cm ON cm.chat_session_id = cr.chat_session_id \n" +
                    "AND ( cr.request_type_id = 4 OR cr.request_type_id = 1 ) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "AND cm.post_time >= cr.creation_time \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "AND length( cm.chat_user_id ) = 6 \n" +
                    "AND cm.chat_user_id NOT LIKE 'BOT%' \n" +
                    "GROUP BY\n" +
                    "cm.Chat_Session_Id \n" +
                    "ORDER BY\n" +
                    "Session_Id DESC \n" +
                    ") F ON a.chat_session_id = f.session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cm.Chat_Session_Id AS Session_Id,\n" +
                    "count( DISTINCT cm.id ) AS 'zx_send_sl' \n" +
                    "FROM\n" +
                    "chat_request cr\n" +
                    "LEFT JOIN Chat_Message cm ON cm.chat_session_id = cr.chat_session_id \n" +
                    "AND ( cr.request_type_id = 4 OR cr.request_type_id = 1 ) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "AND cm.post_time >= cr.creation_time \n" +
                    "AND LENGTH( chat_user_id )= 6 \n" +
                    "AND message_type = 0 \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "AND cm.chat_session_id IS NOT NULL \n" +
                    "GROUP BY\n" +
                    "cm.Chat_Session_Id \n" +
                    ") G ON a.chat_session_id = g.session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cs.id,\n" +
                    "sum( CASE WHEN push_rating_by IS NOT NULL THEN 1 ELSE 0 END ) '是否邀评' \n" +
                    "FROM\n" +
                    "chat_session cs \n" +
                    "WHERE\n" +
                    "cs.start_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cs.start_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "GROUP BY\n" +
                    "cs.id \n" +
                    ") H ON a.chat_session_id = h.id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cs.id,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN ( cs.rating_id IS NOT NULL OR cs.rating_id <> '' ) THEN\n" +
                    "'是' ELSE '否' \n" +
                    "END AS '是否评价',\n" +
                    "cs.rating_id AS '评价满意度',\n" +
                    "cs.RATING_COMMENT AS '满意度备注' \n" +
                    "FROM\n" +
                    "chat_session cs \n" +
                    "WHERE\n" +
                    "cs.start_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cs.start_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    ") I ON a.chat_session_id = i.id\n" +
                    "LEFT JOIN staff s ON a.to_chat_user_id = s.staff_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "count( r1.id ) AS 访客放弃数,\n" +
                    "r1.id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "t.agent_end_time,\n" +
                    "t.end_time,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "t.id,\n" +
                    "t.request_type_id,\n" +
                    "t.request_status_id,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "to_chat_user_id,\n" +
                    "agent_end_time,\n" +
                    "end_time,\n" +
                    "cr.id,\n" +
                    "request_type_id,\n" +
                    "request_status_id \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "request_status_id = 7 \n" +
                    "AND cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "ORDER BY\n" +
                    "chat_session_id ASC,\n" +
                    "creation_time DESC \n" +
                    ") t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") r1 \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "GROUP BY\n" +
                    "r1.id \n" +
                    ") K ON r1.id = k.id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "r1.id,\n" +
                    "count( DISTINCT CASE WHEN ( r1.end_time - r1.creation_time )<= 3000 AND request_status_id = 1 THEN r1.CHAT_SESSION_ID ELSE NULL END ) AS 未等待应答数 \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "t.agent_end_time,\n" +
                    "t.end_time,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "t.id,\n" +
                    "t.request_type_id,\n" +
                    "t.request_status_id,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "to_chat_user_id,\n" +
                    "agent_end_time,\n" +
                    "end_time,\n" +
                    "cr.id,\n" +
                    "request_type_id,\n" +
                    "request_status_id \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "AND cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "ORDER BY\n" +
                    "chat_session_id ASC,\n" +
                    "creation_time DESC \n" +
                    ") t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") r1 \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "GROUP BY\n" +
                    "r1.id \n" +
                    ") L ON r1.id = l.id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "r1.id,\n" +
                    "count( DISTINCT CASE WHEN ( r1.end_time - r1.creation_time )> 3000 AND request_status_id = 1 THEN r1.CHAT_SESSION_ID ELSE NULL END ) AS 等待应答数 \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "t.agent_end_time,\n" +
                    "t.end_time,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "t.id,\n" +
                    "t.request_type_id,\n" +
                    "t.request_status_id,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "to_chat_user_id,\n" +
                    "agent_end_time,\n" +
                    "end_time,\n" +
                    "cr.id,\n" +
                    "request_type_id,\n" +
                    "request_status_id \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "AND cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "ORDER BY\n" +
                    "chat_session_id ASC,\n" +
                    "creation_time DESC \n" +
                    ") t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") r1 \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "GROUP BY\n" +
                    "r1.id \n" +
                    ") M ON r1.id = m.id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "ts.id,\n" +
                    "sum( ts.未等待应答数 + ts.等待应答数 ) AS 在线应答数 \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "r1.id,\n" +
                    "count( DISTINCT CASE WHEN ( r1.end_time - r1.creation_time )<= 3000 AND request_status_id = 1 THEN r1.CHAT_SESSION_ID ELSE NULL END ) AS 未等待应答数,\n" +
                    "count( DISTINCT CASE WHEN ( r1.end_time - r1.creation_time )> 3000 AND request_status_id = 1 THEN r1.CHAT_SESSION_ID ELSE NULL END ) AS 等待应答数 \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "t.agent_end_time,\n" +
                    "t.end_time,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "t.id,\n" +
                    "t.request_type_id,\n" +
                    "t.request_status_id,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "to_chat_user_id,\n" +
                    "agent_end_time,\n" +
                    "end_time,\n" +
                    "cr.id,\n" +
                    "request_type_id,\n" +
                    "request_status_id \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "AND cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "ORDER BY\n" +
                    "chat_session_id ASC,\n" +
                    "creation_time DESC \n" +
                    ") t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") r1 \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "GROUP BY\n" +
                    "r1.id \n" +
                    ") ts \n" +
                    "GROUP BY\n" +
                    "ts.id \n" +
                    ") N ON r1.id = n.id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cr.chat_session_id,\n" +
                    "count(\n" +
                    "DISTINCT\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN ( cr.end_time - cr.creation_time )> 5000 \n" +
                    "AND request_status_id = 7 \n" +
                    "AND cr.CHAT_SESSION_ID IS NOT NULL THEN\n" +
                    "cr.CHAT_SESSION_ID \n" +
                    "WHEN cr.request_status_id = 7 \n" +
                    "AND cr.CHAT_SESSION_ID IS NULL THEN\n" +
                    "cr.id ELSE NULL \n" +
                    "END \n" +
                    ") AS 等待放弃数 \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "AND ((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "GROUP BY\n" +
                    "cr.chat_session_id \n" +
                    ") O ON r1.chat_session_id = o.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "cr.chat_session_id,\n" +
                    "sum( CASE WHEN cr.request_status_id = 5 THEN 1 ELSE 0 END ) 等待转留言数 \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "AND ((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "GROUP BY\n" +
                    "cr.chat_session_id \n" +
                    ") P ON r1.chat_session_id = p.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "t.id,\n" +
                    "sum( t.等待放弃数 + t.等待应答数 + t.等待转留言数 ) AS 总等待数 \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "cr.id,\n" +
                    "count(\n" +
                    "DISTINCT\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN ( cr.end_time - cr.creation_time )> 5000 \n" +
                    "AND request_status_id = 7 \n" +
                    "AND cr.CHAT_SESSION_ID IS NOT NULL THEN\n" +
                    "cr.CHAT_SESSION_ID \n" +
                    "WHEN cr.request_status_id = 7 \n" +
                    "AND cr.CHAT_SESSION_ID IS NULL THEN\n" +
                    "cr.id ELSE NULL \n" +
                    "END \n" +
                    ") AS 等待放弃数,\n" +
                    "count( DISTINCT CASE WHEN ( cr.end_time - cr.creation_time )> 3000 AND request_status_id = 1 THEN cr.CHAT_SESSION_ID ELSE NULL END ) AS 等待应答数,\n" +
                    "sum( CASE WHEN cr.request_status_id = 5 THEN 1 ELSE 0 END ) 等待转留言数 \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "AND ((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    "GROUP BY\n" +
                    "cr.id \n" +
                    ") t \n" +
                    "GROUP BY\n" +
                    "t.id \n" +
                    ") Q ON r1.id = q.id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT DISTINCT\n" +
                    "chat_session_id,\n" +
                    "cr.url_from \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    ") r10 ON r1.chat_session_id = r10.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "sum(\n" +
                    "ifnull(( cr.end_time - cr.creation_time ) DIV 1000, 0 )) AS wait_time \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "GROUP BY\n" +
                    "chat_session_id \n" +
                    ") r11 ON r1.chat_session_id = r11.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "CASE\n" +
                    "\n" +
                    "WHEN sum( CASE WHEN message_type = 1 THEN 1 ELSE 0 END )> 0 THEN\n" +
                    "sum( CASE WHEN message_type = 1 THEN 1 ELSE 0 END )- 1 ELSE sum( CASE WHEN message_type = 1 THEN 1 ELSE 0 END ) \n" +
                    "END AS robot_lxs \n" +
                    "FROM\n" +
                    "chat_message cm \n" +
                    "WHERE\n" +
                    "cm.post_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cm.post_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "GROUP BY\n" +
                    "chat_session_id \n" +
                    ") r12 ON r1.chat_session_id = r12.chat_session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "ecn.session_id,\n" +
                    "ecn.Summary_1,\n" +
                    "ecn.Summary_2,\n" +
                    "ecn.Summary_3,\n" +
                    "ecn.remker \n" +
                    "FROM\n" +
                    "eventcallnature ecn\n" +
                    "LEFT JOIN chat_request cr ON cr.chat_session_id = ecn.session_id \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "AND ((\n" +
                    "cr.request_type_id = 4 \n" +
                    "AND from_chat_user_id LIKE 'BOT%' \n" +
                    ") \n" +
                    "OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) \n" +
                    "AND cr.request_status_id = 1 \n" +
                    ") r13 ON r1.chat_session_id = r13.session_id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "count( r1.id ) AS 全部请求数,\n" +
                    "sum( CASE WHEN request_type_id = 4 THEN 1 ELSE 0 END ) AS 机器人转人工请求数,\n" +
                    "sum( CASE WHEN request_type_id = 1 THEN 1 ELSE 0 END ) AS 直接转人工请求数,\n" +
                    "r1.id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "t.agent_end_time,\n" +
                    "t.end_time,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "t.id,\n" +
                    "t.request_type_id,\n" +
                    "t.request_status_id,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "to_chat_user_id,\n" +
                    "agent_end_time,\n" +
                    "end_time,\n" +
                    "cr.id,\n" +
                    "request_type_id,\n" +
                    "request_status_id \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "ORDER BY\n" +
                    "chat_session_id ASC,\n" +
                    "creation_time DESC \n" +
                    ") t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") r1 \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "GROUP BY\n" +
                    "r1.id \n" +
                    ") r14 ON r1.id = r14.id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "count( r1.id ) AS 在线请求数,\n" +
                    "sum( CASE WHEN request_type_id = 4 THEN 1 ELSE 0 END ) AS 在线机器人转人工请求数,\n" +
                    "sum( CASE WHEN request_type_id = 1 THEN 1 ELSE 0 END ) AS 在线直接转人工请求数,\n" +
                    "r1.id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "t.agent_end_time,\n" +
                    "t.end_time,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "t.id,\n" +
                    "t.request_type_id,\n" +
                    "t.request_status_id,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "to_chat_user_id,\n" +
                    "agent_end_time,\n" +
                    "end_time,\n" +
                    "cr.id,\n" +
                    "request_type_id,\n" +
                    "request_status_id \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "request_status_id <> 6 \n" +
                    "AND cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "ORDER BY\n" +
                    "chat_session_id ASC,\n" +
                    "creation_time DESC \n" +
                    ") t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") r1 \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "GROUP BY\n" +
                    "r1.id \n" +
                    ") r15 ON r1.id = r15.id\n" +
                    "LEFT JOIN (\n" +
                    "SELECT\n" +
                    "count( r1.id ) AS 离线请求数,\n" +
                    "sum( CASE WHEN request_type_id = 4 THEN 1 ELSE 0 END ) AS 离线机器人转人工请求数,\n" +
                    "sum( CASE WHEN request_type_id = 1 THEN 1 ELSE 0 END ) AS 离线直接转人工请求数,\n" +
                    "r1.id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "t.chat_session_id,\n" +
                    "t.creation_time,\n" +
                    "t.queue_id,\n" +
                    "t.agent_end_time,\n" +
                    "t.end_time,\n" +
                    "@rownum := @rownum + 1,\n" +
                    "t.id,\n" +
                    "t.request_type_id,\n" +
                    "t.request_status_id,\n" +
                    "IF\n" +
                    "( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,\n" +
                    "@order_num := t.chat_session_id,\n" +
                    "to_chat_user_id \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "chat_session_id,\n" +
                    "creation_time,\n" +
                    "queue_id,\n" +
                    "to_chat_user_id,\n" +
                    "agent_end_time,\n" +
                    "end_time,\n" +
                    "cr.id,\n" +
                    "request_type_id,\n" +
                    "request_status_id \n" +
                    "FROM\n" +
                    "chat_request cr \n" +
                    "WHERE\n" +
                    "request_status_id = 6 \n" +
                    "AND cr.creation_time >= UNIX_TIMESTAMP( '"+starttime+"' )* 1000 \n" +
                    "AND cr.creation_time <= UNIX_TIMESTAMP( '"+endtime+"' )* 1000 \n" +
                    "ORDER BY\n" +
                    "chat_session_id ASC,\n" +
                    "creation_time DESC \n" +
                    ") t,(\n" +
                    "SELECT\n" +
                    "@rownum := 0,\n" +
                    "@order_num := NULL,\n" +
                    "@rank1 := 0 \n" +
                    ") a \n" +
                    ") r1 \n" +
                    "WHERE\n" +
                    "rank1 = 1 \n" +
                    "GROUP BY\n" +
                    "r1.id \n" +
                    ") r16 ON r1.id = r16.id \n" +
                    "ORDER BY\n" +
                    "3 ASC";
            log.info("网聊执行sql：",selectSql);
            int i = eliteBean.update(selectSql, "dataSource");

            String sql2 = "delete from chat_service_detail where  FROM_UNIXTIME(Creation_Time/1000,'%Y-%m-%d') =CURDATE()"
            int b = eliteBean.update(sql2, "dataSource");
            return "新增数："+i+"删除数："+b;*/

        } catch (Exception e) {
            log.error("网聊数据定时任务操作异常", e);
        }

    }

}

